﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_ucXH_TH : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmBaoCaoXuatHang";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0, f_Khoa = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, da12 = 0, da24 = 0, cat = 0, ximang = 0, nuoc = 0, phugia = 0, ttda12 = 0, ttda24 = 0, ttcat = 0, ttnuoc = 0, ttximang = 0, ttphugia = 0, ttvlth = 0, hotrocuoc = 0, kltramtron = 0, klhang = 0, chenh = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa,Khoa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                f_Khoa = int.Parse(dt2.Rows[0]["Khoa"].ToString());
            }


            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadCongTrinh(dlCongTrinh);
            LoadKhachHang(dlKhachHang);
            LoadLoaiKH(dlLoaiKH);
            LoadTramTron(dlTramTron);
            LoadMacHD(dlMacHD);
            LoadLoaiDa(dlLoaiDa);
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoadLoaiKH(DropDownList dlLoaiKH)
    {
        dlLoaiKH.Items.Clear();
        dlLoaiKH.DataSource = ex.GetData("sp_xdaloaikhachhang_ListDrop");
        dlLoaiKH.DataBind();
        dlLoaiKH.Items.Insert(0, new ListItem("--Chọn LKH--", ""));
        dlLoaiKH.SelectedIndex = 0;
    }
    void LoadKhachHang(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadCongTrinh(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
            //lbTrangThai.Visible = false;
            //btnKhoa.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
            //lbTrangThai.Visible = true;
            //btnKhoa.Visible = true;
        }
        dlLoaiKH.SelectedIndex = 0;
        LoadKhachHang(dlKhachHang);
        getDate();

        //if (dlThang.SelectedIndex > 0)
        //{
        //    //load trang thai khoa
        //    p = new SqlParameter[3];
        //    p[0] = new SqlParameter("@Name", "BCXH");
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        //    p[2].SqlDbType = SqlDbType.Int;

        //    string index = ex.GetValueString("sp_xdakhoa_Check", p);

        //    if (index.Trim().Equals("") || index.Trim().Equals("1"))
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
        //        btnKhoa.Text = "Khóa lại";
        //    }
        //    else
        //    {
        //        lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
        //        btnKhoa.Text = "Mở lại";
        //    }
        //}
    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void dlLoaiKH_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiKH.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlKhachHang.Items.Clear();
            dlKhachHang.DataSource = ex.GetData("sp_BCXH_LoadKhachHangByLoaiKH", p);
            dlKhachHang.DataBind();
            dlKhachHang.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            dlKhachHang.SelectedIndex = 0;

            LoadCongTrinh(dlCongTrinh);
        }
        else
        {
            LoadKhachHang(dlKhachHang);

        }
        LoadCongTrinh(dlCongTrinh);
        LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        btnNext.Enabled = true;
        CurrentPage = 1;
        if (f_SuDung.Equals(1))
        {
            Search(1);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "Home.aspx");

    }
    void Search(int page)
    {
        int index = 1;
        if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 1;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 2;
        else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 3;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
            index = 4;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
            index = 5;
        else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
            index = 6;

        getDate();
        p = new SqlParameter[10];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@PageNumber", page);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@RowspPage", PageNumber);
        p[6].SqlDbType = SqlDbType.Int;
        p[7] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[7].SqlDbType = SqlDbType.UniqueIdentifier;
        p[8] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[8].SqlDbType = SqlDbType.UniqueIdentifier;
        p[9] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[9].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCXH_TH_TongHop", p);
        GV.DataBind();

        p = new SqlParameter[8];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
        p[5].SqlDbType = SqlDbType.UniqueIdentifier;
        p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
        p[6].SqlDbType = SqlDbType.UniqueIdentifier;
        p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
        p[7].SqlDbType = SqlDbType.Int;

        if (GV.Rows.Count > 0)
        {
            dt = ex.GetData("sp_BCXH_TH_TongHop_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                klban = double.Parse(dt.Rows[0]["KLBan"].ToString());
                da12 = double.Parse(dt.Rows[0]["Da12"].ToString());
                da24 = double.Parse(dt.Rows[0]["Da24"].ToString());
                cat = double.Parse(dt.Rows[0]["CatVang"].ToString());
                ximang = double.Parse(dt.Rows[0]["XiMang"].ToString());
                nuoc = double.Parse(dt.Rows[0]["Nuoc"].ToString());
                phugia = double.Parse(dt.Rows[0]["PhuGia"].ToString());
                ttda12 = double.Parse(dt.Rows[0]["TTDGDa12"].ToString());
                ttda24 = double.Parse(dt.Rows[0]["TTDGDa24"].ToString());
                ttcat = double.Parse(dt.Rows[0]["TTDGCatVang"].ToString());
                ttximang = double.Parse(dt.Rows[0]["TTDGXiMang"].ToString());
                ttnuoc = double.Parse(dt.Rows[0]["TTDGNuoc"].ToString());
                ttphugia = double.Parse(dt.Rows[0]["TTDGPhuGia"].ToString());
                ttvlth = double.Parse(dt.Rows[0]["TTVLTH"].ToString());
                hotrocuoc = double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());
                kltramtron = double.Parse(dt.Rows[0]["KLTramTron"].ToString());
                klhang = double.Parse(dt.Rows[0]["KLHang"].ToString());
                chenh = double.Parse(dt.Rows[0]["Chenh"].ToString());

                GV.FooterRow.Cells[9].Text = "Tổng cộng";
                GV.FooterRow.Cells[9].HorizontalAlign = HorizontalAlign.Center;
                GV.FooterRow.Cells[13].Text = ConvertDecimal(kltx);
                GV.FooterRow.Cells[14].Text = ConvertDecimal(klban);
                GV.FooterRow.Cells[17].Text = ConvertDecimal(da12);
                GV.FooterRow.Cells[18].Text = ConvertDecimal(da24);
                GV.FooterRow.Cells[19].Text = ConvertDecimal(cat);
                GV.FooterRow.Cells[20].Text = ConvertDecimal(ximang);
                GV.FooterRow.Cells[21].Text = ConvertDecimal(nuoc);
                GV.FooterRow.Cells[22].Text = ConvertDecimal(phugia);

                GV.FooterRow.Cells[29].Text = ConvertDecimal(ttda12);
                GV.FooterRow.Cells[30].Text = ConvertDecimal(ttda24);
                GV.FooterRow.Cells[31].Text = ConvertDecimal(ttcat);
                GV.FooterRow.Cells[32].Text = ConvertDecimal(ttximang);
                GV.FooterRow.Cells[33].Text = ConvertDecimal(ttnuoc);
                GV.FooterRow.Cells[34].Text = ConvertDecimal(ttphugia);

                GV.FooterRow.Cells[35].Text = ConvertDecimal(ttvlth);
                GV.FooterRow.Cells[36].Text = ConvertDecimal(hotrocuoc);

                GV.FooterRow.Cells[39].Text = ConvertDecimal(kltramtron);
                GV.FooterRow.Cells[40].Text = ConvertDecimal(klhang);
                GV.FooterRow.Cells[41].Text = ConvertDecimal(chenh);
            }
        }
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;

    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;

    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCXHTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCXHBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCXHMacHopDong.aspx");
        else
            if (dlLoaiBaoCao.SelectedIndex.Equals(3))
                Response.Redirect("BCXHMacThucXuat.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
                Response.Redirect("BCXHLaiXe.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
                Response.Redirect("BCXHLoaiPhuGia.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
                Response.Redirect("BCXHKhachHang.aspx");
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {

        if (f_InBaoCao.Equals(1))
        {
            int ind = 1;
            if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                ind = 1;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                ind = 2;
            else if (dlLoaiKH.SelectedIndex.Equals(0) && dlKhachHang.SelectedIndex.Equals(0) && dlCongTrinh.SelectedIndex.Equals(0) && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                ind = 3;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex.Equals(0) && dlLoaiDa.SelectedIndex.Equals(0))
                ind = 4;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex.Equals(0))
                ind = 5;
            else if (dlLoaiKH.SelectedIndex > 0 && dlKhachHang.SelectedIndex > 0 && dlCongTrinh.SelectedIndex > 0 && dlMacHD.SelectedIndex > 0 && dlLoaiDa.SelectedIndex > 0)
                ind = 6;

            getDate();
            p = new SqlParameter[8];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@LoaiKH", ex.GetGuid(dlLoaiKH.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
            p[4] = new SqlParameter("@Index", ind);
            p[4].SqlDbType = SqlDbType.Int;
            p[5] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[5].SqlDbType = SqlDbType.UniqueIdentifier;
            p[6] = new SqlParameter("@MacHD", ex.GetGuid(dlMacHD.SelectedValue));
            p[6].SqlDbType = SqlDbType.UniqueIdentifier;
            p[7] = new SqlParameter("@LoaiDa", int.Parse(dlLoaiDa.SelectedValue));
            p[7].SqlDbType = SqlDbType.Int;

            dtPrint = ex.GetData("sp_BCXH_TH_TongHop_Print", p);
            PrintNPOI(dtPrint);
        }
        else gstGetMess("Bạn không có quyền in báo cáo này", "");

    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlKhachHang.SelectedIndex > 0)
                sheetname = dlKhachHang.SelectedItem.Text;
            else if (dlLoaiKH.SelectedIndex > 0)
                sheetname = "Bảng tổng hợp-" + dlLoaiKH.SelectedItem.Text;
            else sheetname = "Bảng tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;
            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG BÁN HÀNG " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //nha cung cap
            string ct = dlCongTrinh.SelectedIndex > 0 ? "-" + dlCongTrinh.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(dlKhachHang.SelectedIndex > 0 ? "Khách hàng: " + dlKhachHang.SelectedItem.Text + ct : dlLoaiKH.SelectedIndex > 0 ? "Loại khách hàng: " + dlLoaiKH.SelectedItem.Text : "");
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            //End header-----------------------------------------------------------------------

            #region Header1-2
            rowIndex++;
            //header1
            row = sheet.CreateRow(rowIndex);
            string[] header1 = { "Vật liệu xuất theo phiếu", "Đơn giá vật liệu theo phiếu", "Thành tiền vật liệu theo phiếu", "Giá cước VC", "TT Cước VC", "TT hàng/1M3", "TT hàng/1 chuyến" };
            int[] me = { 17, 23, 29 };

            //tao header1
            for (int m = 0; m <= 2; m++)
            {

                r1c1 = row.CreateCell(me[m]);
                r1c1.SetCellValue(header1[m]);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(4, 4, me[m], me[m] + 5);
                sheet.AddMergedRegion(cra);

            }

            //fix lai border
            for (int h = 0; h <= 41; h++)
            {
                if (h != 17 && h != 23 && h != 29)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            //header2
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Số phiếu", "Biển số", "Lái xe", "Giờ xuất", "Ca làm việc", "Số KMVC", "VAT", "Khách hàng", "Độ sụt", "Mác TX", "Mác HĐ", "KL TX", "KL bán", "Loại đá", "Loại PG", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "TT VLTH", "Hỗ trợ cước", "Loại ca bơm", "NVKD", "KL trạm trộn", "KL hàng", "Chênh" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 1; j <= header.Length; j++)
            {
                cell = row.CreateCell(j);
                cell.SetCellValue(header[j - 1].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                for (int j = 0; j <= dt.Columns.Count - 4; j++)
                {
                    cell = row.CreateCell(j);
                    if (j == 13 || j == 14 || (j >= 17 && j <= 36) || (j >= 39 && j <= 41))
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        if (j <= 2 || (j >= 5 && j <= 8) || (j >= 10 && j <= 12))
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }

            #region Footer
            //-----------Footer
            string[] footer = { "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP" };
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(4);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            for (int k = 13; k <= 41; k++)
            {
                if (k != 15 && k != 16 && k != 37 && k != 38)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "Sum(" + footer[k - 13] + "7:" + footer[k - 13] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            //fix lai border
            for (int h = 0; h <= 41; h++)
            {
                if (h == 4 || h == 13 || h == 14 || (h >= 17 && h <= 36) || (h >= 39))
                {

                }
                else
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            #endregion

            //end sheet tong hop--------------------------
            if (dlKhachHang.SelectedIndex.Equals(0))
            {
                //in tung khach hang vao tung sheet
                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt.Rows[i][44].ToString();
                        if (!ncc.Equals(dt.Rows[i - 1][44].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(4);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 13; k <= 41; k++)
                            {
                                if (k != 15 && k != 16 && k != 37 && k != 38)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 13] + "7:" + footer[k - 13] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 41; h++)
                            {
                                if (h == 4 || h == 13 || h == 14 || (h >= 17 && h <= 36) || (h >= 39))
                                {

                                }
                                else
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int ih = 0; ih < dt.Columns.Count; ih++)
                            {
                                sheet.AutoSizeColumn(ih);
                            }

                            sheet = workbook.CreateSheet(ncc.Length > 30 ? ncc.Substring(0, 30).Replace("*", "x") : ncc.Replace("*", "x"));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG BÁN HÀNG " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Khách hàng: " + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            //End header-----------------------------------------------------------------------

                            #region Header1-2
                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 2; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 5);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 41; h++)
                            {
                                if (h != 17 && h != 23 && h != 29)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            STT++;
                            #endregion
                        }
                    }
                    else
                    {
                        ncc = "";
                        if (!ncc.Equals(dt.Rows[i][44].ToString()))
                        {
                            ncc = dt.Rows[i][44].ToString();
                            sheet = workbook.CreateSheet(ncc.Length > 30 ? ncc.Substring(0, 30).Replace("*", "x") : ncc.Replace("*", "x"));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG BÁN HÀNG " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //nha cung cap
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Khách hàng: " + ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            //End header-----------------------------------------------------------------------

                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            for (int m = 0; m <= 2; m++)
                            {

                                r1c1 = row.CreateCell(me[m]);
                                r1c1.SetCellValue(header1[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(4, 4, me[m], me[m] + 5);
                                sheet.AddMergedRegion(cra);

                            }

                            //fix lai border
                            for (int h = 0; h <= 41; h++)
                            {
                                if (h != 17 && h != 23 && h != 29)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }

                            //header2
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 1; j <= header.Length; j++)
                            {
                                cell = row.CreateCell(j);
                                cell.SetCellValue(header[j - 1].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.CellStyle = hsCenter;
                    cell.SetCellValue(STT.ToString());

                    for (int j = 1; j <= dt.Columns.Count - 4; j++)
                    {
                        cell = row.CreateCell(j);
                        if (j == 13 || j == 14 || (j >= 17 && j <= 36) || (j >= 39 && j <= 41))
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            if (j <= 2 || (j >= 5 && j <= 8) || (j >= 10 && j <= 12))
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }


                    if (i == dt.Rows.Count - 1)
                    {
                        #region Footer
                        //-----------Footer
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(4);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 13; k <= 41; k++)
                        {
                            if (k != 15 && k != 16 && k != 37 && k != 38)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 13] + "7:" + footer[k - 13] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        //fix lai border
                        for (int h = 0; h <= 41; h++)
                        {
                            if (h == 4 || h == 13 || h == 14 || (h >= 17 && h <= 36) || (h >= 39))
                            {

                            }
                            else
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        for (int ik = 0; ik < dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                        #endregion
                    }

                }
            }
            else
            {
                //Chi in khach hang duoc chon
            }
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlKhachHang.SelectedIndex > 0 ? dlKhachHang.SelectedItem.Text : dlLoaiKH.SelectedIndex > 0 ? dlLoaiKH.SelectedItem.Text : "";
                string saveAsFileName = "BCXH-TongHop-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }

    }
    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;
            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 16;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            ////Gio xuat tram
            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            ////Phu gia
            //HeaderGridRow.Cells.Add(HeaderCell);
            //HeaderCell = new TableCell();
            //HeaderCell.Text = "";
            //HeaderCell.ColumnSpan = 1;

            //vat lieu xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Vật liệu xuất theo phiếu";
            HeaderCell.ColumnSpan = 6;

            //Don gia xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Đơn giá vật liệu theo phiếu";
            HeaderCell.ColumnSpan = 6;

            //thnah tien vat lieu xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền vật liệu xuất theo phiếu";
            HeaderCell.ColumnSpan = 6;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }
    protected void dlKhachHang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlKhachHang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@KhachHang", ex.GetGuid(dlKhachHang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlCongTrinh.Items.Clear();
            dlCongTrinh.DataSource = ex.GetData("sp_BCXH_LoadCongTrinhByKhachHang", p);
            dlCongTrinh.DataBind();
            dlCongTrinh.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
            dlCongTrinh.SelectedIndex = 0;
        }
        else
        {
            LoadCongTrinh(dlCongTrinh);

        }
        LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    //protected void btnKhoa_Click(object sender, EventArgs e)
    //{
    //    if (f_Khoa.Equals(1))
    //    {
    //        if (dlThang.SelectedIndex > 0)
    //        {
    //            p = new SqlParameter[4];
    //            p[0] = new SqlParameter("@Name", "BCXH");
    //            p[0].SqlDbType = SqlDbType.NVarChar;
    //            p[1] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
    //            p[1].SqlDbType = SqlDbType.Int;
    //            p[2] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
    //            p[2].SqlDbType = SqlDbType.Int;
    //            p[3] = new SqlParameter("@Khoa", btnKhoa.Text.Trim().ToLower().Equals("mở lại") ? 1 : 2);
    //            p[3].SqlDbType = SqlDbType.Int;
    //            ex.ExecuteNonQuerry("sp_xdakhoa_Update", p);

    //            if (btnKhoa.Text.Equals("Khóa lại"))
    //            {
    //                lbTrangThai.Text = "Trạng thái: Dữ liệu đang bị khóa";
    //                btnKhoa.Text = "Mở lại";
    //                gstGetMess("Đã khóa lại", "");


    //            }
    //            else
    //            {
    //                lbTrangThai.Text = "Trạng thái: Dữ liệu đang mở";
    //                btnKhoa.Text = "Khóa lại";
    //                gstGetMess("Đã mở lại", "");
    //            }

    //        }
    //        else gstGetMess("Chọn tháng cần khóa", "");
    //    }
    //    else gstGetMess("Bạn không có quyền khóa/mở dữ liệu", "");
    //}
    protected void dlCongTrinh_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlCongTrinh.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlMacHD.Items.Clear();
            dlMacHD.DataSource = ex.GetData("sp_BCXH_LoadMacHDByCongTrinh", p);
            dlMacHD.DataBind();
            dlMacHD.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
            dlMacHD.SelectedIndex = 0;
        }
        else
            LoadMacHD(dlMacHD);
        LoadLoaiDa(dlLoaiDa);
    }
    protected void dlMacHD_SelectedIndexChanged(object sender, EventArgs e)
    {

        if (dlMacHD.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@CongTrinh", ex.GetGuid(dlCongTrinh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Mac", ex.GetGuid(dlMacHD.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;

            dlLoaiDa.Items.Clear();
            dlLoaiDa.DataSource = ex.GetData("sp_BCXH_LoadLoaiDaByMacCongTrinh", p);
            dlLoaiDa.DataBind();
            dlLoaiDa.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
            dlLoaiDa.SelectedIndex = 0;
        }
        else
            LoadLoaiDa(dlLoaiDa);
    }
    void LoadMacHD(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn mác HĐ--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadLoaiDa(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn loại đá--", "0"));
        dl.SelectedIndex = 0;
    }
}